#delimit;
clear;
set more off;
capture log close;
set more off;
/*****
CHANGE PATH
*****/;
/*This program creates the variables for sectors attended. For this, I need info fron NSC, NSLDS loans and NSLDS Pell Grants*/
local pathtab "/data";
local out "/data";

/********************************
Creating Pell Measures
********************************/
use "`pathtab'/temp_pell_tomerge.dta", replace;
drop opeid;
rename amt_paid_to_dt amount;
gen enroll_beg_pell=mdy(9,30,award_yr);
format %td enroll_beg_pell;

merge m:1 pid using "`pathtab'/age_tomerge.dta";
drop if _merge==2;
drop _merge;
drop dob_y dob_m;
sort pid award_yr;
by pid award_yr: egen sum_amount=sum(amount);
drop amount;
by pid award_yr: keep if _n==1;
rename sum_amount amount;

forvalues yo=22(1)35 {; //this means we are going to have values for 21 to 34
gen pell_t_`yo'=0;
gen pell_amount_t_`yo'=0;
replace pell_t_`yo'=1 if (enroll_beg_pell-dob)/365<`yo';
replace pell_amount_t_`yo'=amount if (enroll_beg_pell-dob)/365<`yo';
by pid: egen pell_`yo'=max(pell_t_`yo');
by pid: egen pell_amount_`yo'=sum(pell_amount_t_`yo');
drop pell_t_`yo' pell_amount_t_`yo';
local yo_1=`yo'-1;
rename pell_`yo' pell_`yo_1';
rename pell_amount_`yo' pell_amount_`yo_1';
};
by pid: keep if _n==1;
drop award_yr enroll_beg_pell age_2004 dob amount;
save "`pathtab'/pell_variables.dta", replace;


use "`pathtab'/ipeds_state_noyear.dta", clear;  //from 02_IPEDS_school_state.do
drop first_col_state_fips;
rename first_col_sector sector_ipeds;
rename first_col_opeid_nslds opeid_dash;
rename first_col_state state_ipeds;
sort opeid_dash;
gen opeid6=substr(opeid_dash,1,6);
save "`pathtab'/ipeds_sector_noyear_tomerge.dta", replace;

use "`pathtab'/ipeds_state_opeid6.dta", clear;  //from 02_IPEDS_school_state.do
drop first_col_state_fips first_col_opeid_nslds;
sort first_col_opeid6_nslds year_enroll;
by first_col_opeid6_nslds: keep if _n==1;

rename first_col_sector sector_ipeds;
rename first_col_opeid6_nslds opeid6;
rename first_col_state state_ipeds;
drop year_enroll;
sort opeid6;
save "`pathtab'/ipeds_sector_noyear_opeid6.dta", replace;

/***********************************
/*Merge NSC with IPEDS, using opeid*/
************************************/
use "`out'/NSC_merged.dta", clear;

gen Enroll_Begin_Grad_Date=Enrollment_Begin;
replace  Enroll_Begin_Grad_Date=Graduation_Date if Enrollment_Begin==.;

gen Enroll_End_Grad_Date=Enrollment_End;
replace  Enroll_End_Grad_Date=Graduation_Date if Enrollment_End==.;

sort pid;

keep pid College_Code_Branch Enroll_Begin_Grad_Date Enroll_End_Grad_Date College_State;

rename College_Code_Branch opeid_dash;
rename Enroll_Begin_Grad_Date enroll_beg;
rename Enroll_End_Grad_Date enroll_end;
rename College_State state_nsc;

merge m:1 opeid_dash using "`pathtab'/ipeds_sector_noyear_tomerge.dta";
keep if _merge==3;
drop _merge;
save "`pathtab'/temp_college_sector_tomerge_8.dta", replace;

/*Merging NSC with IPEDS, using opeid6*/
use "`out'/NSC_merged.dta", clear;

gen Enroll_Begin_Grad_Date=Enrollment_Begin;
replace  Enroll_Begin_Grad_Date=Graduation_Date if Enrollment_Begin==.;

gen Enroll_End_Grad_Date=Enrollment_End;
replace  Enroll_End_Grad_Date=Graduation_Date if Enrollment_End==.;

sort pid;

keep pid College_Code_Branch Enroll_Begin_Grad_Date Enroll_End_Grad_Date College_State;

rename College_Code_Branch opeid_dash;
rename Enroll_Begin_Grad_Date enroll_beg;
rename Enroll_End_Grad_Date enroll_end;
rename College_State state_nsc;

merge m:1 opeid_dash using "`pathtab'/ipeds_sector_noyear_tomerge.dta";
keep if _merge==1;
drop _merge;
drop opeid6 sector_ipeds state_ipeds;
gen opeid6=substr(opeid_dash,1,6);
drop opeid_dash;
merge m:1 opeid6 using "`pathtab'/ipeds_sector_noyear_opeid6.dta";
keep if _merge==3;
drop _merge;

format enroll_beg %td;
format enroll_end %td;

append using "`pathtab'/temp_college_sector_tomerge_8.dta";
drop opeid6 opeid_dash;
save "`pathtab'/sector_nsc_tomerge.dta", replace;

/***********************************
/*Merge of NLSDS loans with IPEDS*/
************************************/
use "`pathtab'/nslds_tomerge_degree.dta", replace;  //from 06_NSLDS_Degree.do
drop if opeid=="";
drop acad_lvl_num;
gen opeid_dash=substr(opeid,1,6)+"-"+substr(opeid,7,2) if opeid~="";
drop opeid;
merge m:1 opeid_dash using "`pathtab'/ipeds_sector_noyear_tomerge.dta";
keep if _merge==3;
drop _merge;
save "`pathtab'/temp_college_sector_nslds_loan_tomerge_8.dta", replace;

/*Merge of NLSDS loans with IPEDS, using opeid6*/
use "`pathtab'/nslds_tomerge_degree.dta", replace;
drop if opeid=="";
drop acad_lvl_num;
gen opeid_dash=substr(opeid,1,6)+"-"+substr(opeid,7,2) if opeid~="";
drop opeid;
merge m:1 opeid_dash using "`pathtab'/ipeds_sector_noyear_tomerge.dta";
keep if _merge==1;
drop _merge;
drop opeid6 sector_ipeds state_ipeds;
gen opeid6=substr(opeid_dash,1,6);
drop opeid_dash;
merge m:1 opeid6 using "`pathtab'/ipeds_sector_noyear_opeid6.dta";
keep if _merge==3;
drop _merge;

append using "`pathtab'/temp_college_sector_nslds_loan_tomerge_8.dta";
drop opeid6 opeid_dash;
save "`pathtab'/sector_nslds_loans_tomerge.dta", replace;

/***********************************
/*Merge of NLSDS Pell with IPEDS*/
************************************/
use "`pathtab'/temp_pell_tomerge.dta", clear;
keep pid opeid award_yr;

/*I will assume that if award_yr is, for example, 2003, enrollment started in september
of that year*/
gen enroll_beg_pell=mdy(9,30,award_yr);
format %td enroll_beg_pell;
rename opeid opeid_dash;
drop award_yr;
merge m:1 opeid_dash using "`pathtab'/ipeds_sector_noyear_tomerge.dta";
keep if _merge==3;
drop _merge;
save "`pathtab'/temp_college_sector_nslds_pell_tomerge_8.dta", replace;

/*Merge of NLSDS pell with IPEDS, using opeid6*/
use "`pathtab'/temp_pell_tomerge.dta", clear;
keep pid opeid award_yr;
gen enroll_beg_pell=mdy(9,30,award_yr);
format %td enroll_beg_pell;
rename opeid opeid_dash;
drop award_yr;
merge m:1 opeid_dash using "`pathtab'/ipeds_sector_noyear_tomerge.dta";
keep if _merge==1;
drop _merge;

drop sector_ipeds opeid6 state_ipeds;
gen opeid6=substr(opeid_dash,1,6);
drop opeid_dash;
merge m:1 opeid6 using "`pathtab'/ipeds_sector_noyear_opeid6.dta";
keep if _merge==3;
drop _merge;

append using "`pathtab'/temp_college_sector_nslds_pell_tomerge_8.dta";
drop opeid6 opeid_dash;
save "`pathtab'/sector_nslds_pell_tomerge.dta", replace;

append using "`pathtab'/sector_nslds_loans_tomerge.dta";
append using "`pathtab'/sector_nsc_tomerge.dta";

order pid sector_ipeds enroll_beg_pell per_beg_dt per_end_dt enroll_beg enroll_end;

merge m:1 pid using "`pathtab'/age_tomerge.dta";
keep if _merge==3;
drop dob_y dob_m;
drop _merge;

save "`pathtab'/sector_tocreate_ever_state.dta", replace;

sort pid;
forvalues yo=22(1)41 {; //this means we are going to have values for 21 to 34
gen sector_t_`yo'=0;
replace sector_t_`yo'=sector_ipeds if (enroll_beg_pell-dob)/365<`yo' | (per_beg_dt-dob)/365<`yo' | (enroll_beg-dob)/365<`yo';
gen with_pub4_`yo'=0;
replace with_pub4_`yo'=1 if sector_t_`yo'==1;
gen with_pub2_`yo'=0;
replace with_pub2_`yo'=1 if sector_t_`yo'==4 | sector_t_`yo'==7;
gen with_priv_nfp4_`yo'=0;
replace with_priv_nfp4_`yo'=1 if sector_t_`yo'==2;
gen with_priv_nfp2_`yo'=0;
replace with_priv_nfp2_`yo'=1 if sector_t_`yo'==5 | sector_t_`yo'==8;
gen with_priv_fp_`yo'=0;
replace with_priv_fp_`yo'=1 if sector_t_`yo'==3 | sector_t_`yo'==6 | sector_t_`yo'==9;

by pid: egen ever_with_pub4_`yo'=max(with_pub4_`yo');
by pid: egen ever_with_pub2_`yo'=max(with_pub2_`yo');
by pid: egen ever_with_priv_nfp4_`yo'=max(with_priv_nfp4_`yo');
by pid: egen ever_with_priv_nfp2_`yo'=max(with_priv_nfp2_`yo');
by pid: egen ever_with_priv_fp_`yo'=max(with_priv_fp_`yo');

drop sector_t_`yo' with_pub4_`yo' with_pub2_`yo' with_priv_nfp4_`yo' with_priv_nfp2_`yo' with_priv_fp_`yo';
local yo_1=`yo'-1;
rename ever_with_pub4_`yo' ever_with_pub4_`yo_1';
rename ever_with_pub2_`yo' ever_with_pub2_`yo_1';
rename ever_with_priv_nfp4_`yo' ever_with_priv_nfp4_`yo_1';
rename ever_with_priv_nfp2_`yo' ever_with_priv_nfp2_`yo_1';
rename ever_with_priv_fp_`yo' ever_with_priv_fp_`yo_1';
};
by pid: keep if _n==1;
keep pid ever_with*;
sort pid;
save "`pathtab'/sector.dta", replace;



erase "`pathtab'/ipeds_sector_noyear_tomerge.dta";
erase "`pathtab'/ipeds_sector_noyear_opeid6.dta";
erase "`pathtab'/temp_college_sector_tomerge_8.dta";
erase "`pathtab'/temp_college_sector_nslds_loan_tomerge_8.dta";
erase "`pathtab'/sector_nsc_tomerge.dta";
erase "`pathtab'/sector_nslds_loans_tomerge.dta";
erase "`pathtab'/sector_nslds_pell_tomerge.dta";
